package com.spongeli.user.service.impl;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.spongeli.entity.SaleThread;
import com.spongeli.entity.SaleThreadExample;
import com.spongeli.entity.SaleThreadExample.Criteria;
import com.spongeli.user.UploadExcelDTO;
import com.spongeli.user.mapper.SaleThreadMapper;
import com.spongeli.user.service.IFileUploadService;
import com.spongeli.user.util.Validator;

//业务层实现类：
@Service
public class FileUploadServiceImpl implements IFileUploadService {
	
	@Resource
	private SaleThreadMapper saleThreadMapper;
	@Override
	public Map<String, Object> uploadExcel(String targetPath, MultipartFile upFile) throws Exception {
		Map<String,Object> res = new HashMap<>();
		try {
			// 1.0 将文件存储至服务地磁盘,返回最新的file
			File file = this.saveExcel(targetPath, upFile);
			// 2.0 将文件解析到内存 并且校验合法性
			UploadExcelDTO dto = encapsulationExcel(file);
			System.out.println("校验完成,总计："+dto.getTotal()+"条，成功："+dto.getSuccess()+"条,未通过："+dto.getError()+" 条");
			// 3.0 将正确的入库
			List<String> list = null ;
			if(dto.getSuccessList() != null && dto.getSuccessList().size() > 0){
				list = insertSaleThread(dto.getSuccessList());
			}
			res.put("total", dto.getTotal());  								  //总计
			res.put("success", dto.getSuccess()-(list!=null?list.size():0));  //成功
			res.put("error", dto.getError()); 								  //校验失败
			res.put("errorData", dto.getErrorData()); 						  //校验失败信息
			res.put("repeat", list!=null?list.size():0);					  //重复数量
			res.put("repeatData", list);									  //重复的电话号
		} catch (IllegalStateException e) {
			throw new Exception("将文件保存至本地失败！");
		} catch (IOException e) {
			throw new Exception("IOException");
		}
		return res;
	}

	/**
	 * 入库操作
	 * @param successList
	 * @return
	 * @author spongeli
	 */
	private List<String> insertSaleThread(List<SaleThread> successList) {
		List<String> list = new ArrayList<>();
		int index = 0;
		for (Iterator<SaleThread> iterator = successList.iterator(); iterator.hasNext();) {
			SaleThread record = (SaleThread) iterator.next();
			record.setCreatetime(new Date());
			
			//验重
			SaleThreadExample example = new SaleThreadExample();
			Criteria criteria = example.createCriteria();
			criteria.andCustomertelEqualTo(record.getCustomertel());
			criteria.andThreadsourceidEqualTo(record.getThreadsourceid());
			List<SaleThread> example2 = saleThreadMapper.selectByExample(example);
			if(example2 == null){
				int insert = saleThreadMapper.insertSelective(record);
				if(insert > 0){
					index ++;
				}
			}{
				list.add(record.getCustomertel());
			}
		}
		System.out.println("写入数据库："+index+" 条");
		return list;
	}

	/**
	 * 将文件解析到内存 并且校验合法性 error :
	 * 
	 * @param file
	 * @return
	 * @author spongeli
	 */
	private UploadExcelDTO encapsulationExcel(File file) {
		UploadExcelDTO dto = null;
		if (null == file || !file.exists()) {
			return null;
		}
		List<SaleThread> list = null;
		/* 解析 */
		if (file.getName().endsWith("xls")) {
			list = parse2003Excel(file);
		} else if (file.getName().endsWith("xlsx")) {
			list = parse2007Excel(file);
		} else {
			throw new RuntimeException("文件名不正确！");
		}
		/* 校验 */
		if(list != null && list.size() > 0){
			dto = verificationDate(list);
		}
		return dto != null ? dto : new UploadExcelDTO();
	}
	/**
	 * 开启校验
	 * @param list
	 * @return
	 * @author spongeli
	 */
	private UploadExcelDTO verificationDate(List<SaleThread> list) {
		UploadExcelDTO dto = new UploadExcelDTO();
		List<Map<String, String>> errorData = new ArrayList<Map<String,String>>() ;
		List<SaleThread> successList = new ArrayList<>();
		StringBuffer sb = null;
		int errIndex = 1;
		for (int i = 0; i < list.size(); i++) {
			errIndex = 1;
			sb = new StringBuffer();
			SaleThread thread = list.get(i);
			//姓名
			if(!Validator.isChinese(thread.getCustomername())){
				sb.append(errIndex++).append("、").append("客户姓名校验失败;");
			}
			//电话
			if(!Validator.isMobile(thread.getCustomertel())){
				sb.append(errIndex++).append("、").append("客户手机号校验失败;");
			}
			//性别
			if(thread.getGender() == null ){
				sb.append(errIndex++).append("、").append("性别校验失败;");
			}
			//微信号
			if(!isNotEmpty(thread.getWechatnum())){
				sb.append(errIndex++).append("、").append("微信号校验失败;");
			}
			//线索来源
			if(thread.getThreadsourceid() == null){
				sb.append(errIndex++).append("、").append("线索来源校验失败;");
			}
			//活动来源
			if(thread.getActivitysourceid() == null){
				sb.append(errIndex++).append("、").append("活动来源校验失败;");
			}
			//有效性
			if(thread.getDatastatus() == null ){
				sb.append(errIndex++).append("、").append("有效性校验失败;");
			}
			//线索分类
			if(thread.getThreadtype() == null ){
				sb.append(errIndex++).append("、").append("线索分类校验失败;");
			}
			//小区
			if(thread.getHouseestate() == null){
				sb.append(errIndex++).append("、").append("小区编码校验失败;");
			}
			//省
			if(!isNotEmpty(thread.getProvince())){
				sb.append(errIndex++).append("、").append("省校验失败;");
			}
			//市
			if(!isNotEmpty(thread.getCity())){
				sb.append(errIndex++).append("、").append("市校验失败;");
			}
			//县
			if(!isNotEmpty(thread.getCounty())){
				sb.append(errIndex++).append("、").append("县校验失败;");
			}
			//房屋地址
			if(!isNotEmpty(thread.getAdress())){
				sb.append(errIndex++).append("、").append("房屋地址校验失败;");
			}
			//房屋类型
			if(thread.getHousetype() == null ){
				sb.append(errIndex++).append("、").append("房屋类型校验失败;");
			}
			//房屋户型
			if(thread.getHouselayout() == null){
				sb.append(errIndex++).append("、").append("房屋户型源校验失败;");
			}
			//预算金额
			if(thread.getBudgetmoney() == null){
				sb.append(errIndex++).append("、").append("预算金额校验失败;");
			}
			//房屋面积
			if(thread.getHousearea() == null){
				sb.append(errIndex++).append("、").append("房屋面积校验失败;");
			}
			//消费意向
			if(thread.getIntetionid() == null){
				sb.append(errIndex++).append("、").append("消费意向ID校验失败;");
			}
			
			if(sb.length() > 0){
				//挂了
				Map<String,String> map = new HashMap<>();
				map.put("index", "第"+(i+1)+"行");
				map.put("errorMsg", sb.toString());
				errorData.add(map);
			}else{
				//过了
				successList.add(thread);
			}
		}
		dto.setSuccessList(successList );
		dto.setErrorData(errorData);
		dto.setTotal(list.size());
		dto.setSuccess(successList.size());
		dto.setError(errorData.size());
		return dto;
	}

	/**
	 * 解析2007
	 * 
	 * @param file
	 * @return
	 * @author spongeli
	 */
	private List<SaleThread> parse2007Excel(File file) {
		List<SaleThread> rows = new ArrayList<SaleThread>();
		BufferedInputStream bis = null ;
        try {
        	bis = new BufferedInputStream(new FileInputStream(file));
            XSSFWorkbook wb = new XSSFWorkbook(bis);
            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row = null ;
            SaleThread sale = null;
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
				// 获取行数据
				row = sheet.getRow(rowIndex);
				// 如果这行数据为空 继续
				if (null == row) {
					continue;
				}
				/* 拼装数据 */
				sale = new SaleThread();
				sale.setCustomername(row.getCell(0).getStringCellValue().trim());
				sale.setCustomertel(row.getCell(1).getStringCellValue().trim());
				sale.setGender(parseGender(row.getCell(2).getStringCellValue().trim()));
				sale.setWechatnum(row.getCell(3).getStringCellValue().trim());
				sale.setThreadsourceid(parseLong(row.getCell(4).getStringCellValue().trim()));
				sale.setActivitysourceid(parseLong(row.getCell(5).getStringCellValue().trim()));
				sale.setDatastatus(parseByte(row.getCell(6).getStringCellValue().trim()));
				sale.setThreadtype(parseLong(row.getCell(7).getStringCellValue().trim()));
				sale.setHouseestate(parseLong(row.getCell(8).getStringCellValue().trim()));
				sale.setProvince(row.getCell(9).getStringCellValue().trim());
				sale.setCity(row.getCell(10).getStringCellValue().trim());
				sale.setCounty(row.getCell(11).getStringCellValue().trim());
				sale.setAdress(row.getCell(12).getStringCellValue().trim());
				sale.setHousetype(parseByte(row.getCell(13).getStringCellValue().trim()));
				sale.setHouselayout(parseByte(row.getCell(14).getStringCellValue().trim()));
				sale.setBudgetmoney(parseFloat(row.getCell(15).getStringCellValue().trim()));
				sale.setHousearea(parseDouble(row.getCell(16).getStringCellValue().trim()));
				sale.setIntetionid(parseLong(row.getCell(17).getStringCellValue().trim()));
				rows.add(sale);
			}
            //关闭输入流
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
			if (bis != null) {
				try {
					bis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
        }
        return rows;
	}
	/**
	 * 处理性别的方法
	 * @param trim
	 * @return
	 * @author spongeli
	 */
	private Byte parseGender(String trim) {
		if("男".equals(trim)){
			return (byte)0;
		}else if("女".equals(trim)){
			return (byte)1;
		}else if("未知".equals(trim)){
			return (byte)2;
		}else{
			return null;
		}
	}

	/**
	 * 解析2003
	 * 
	 * @param file
	 * @return
	 * @author spongeli
	 */
	private List<SaleThread> parse2003Excel(File file) {
		List<SaleThread> rows = new ArrayList<SaleThread>();
		// 获取输入流
		BufferedInputStream bis = null;
		SaleThread sale = null;
		try {
			bis = new BufferedInputStream(new FileInputStream(file));
			// poi提供的类 工作簿
			HSSFWorkbook wk = new HSSFWorkbook(bis);
			// 获取一片 工作表
			HSSFSheet sheet = wk.getSheetAt(0);
			HSSFRow row = null ;
			// getLastRowNum 获取最后一行的行号
			for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
				// 获取行数据
				row = sheet.getRow(rowIndex);
				// 如果这行数据为空 继续
				if (null == row) {
					continue;
				}
				for (int i = 0; i < row.getLastCellNum(); i++) {
					HSSFCell cell = row.getCell(i);
					if(cell!=null){
						cell.setCellType(Cell.CELL_TYPE_STRING);
					}else{
						row.createCell(i).setCellValue("");
					}
				}
				/* 拼装数据 */
				sale = new SaleThread();
				sale.setCustomername(row.getCell(0).getStringCellValue().trim());
				sale.setCustomertel(row.getCell(1).getStringCellValue().trim());
				sale.setGender(parseGender(row.getCell(2).getStringCellValue().trim()));
				sale.setWechatnum(row.getCell(3).getStringCellValue().trim());
				sale.setThreadsourceid(parseLong(row.getCell(4).getStringCellValue().trim()));
				sale.setActivitysourceid(parseLong(row.getCell(5).getStringCellValue().trim()));
				sale.setDatastatus(parseByte(row.getCell(6).getStringCellValue().trim()));
				sale.setThreadtype(parseLong(row.getCell(7).getStringCellValue().trim()));
				sale.setHouseestate(parseLong(row.getCell(8).getStringCellValue().trim()));
				sale.setProvince(row.getCell(9).getStringCellValue().trim());
				sale.setCity(row.getCell(10).getStringCellValue().trim());
				sale.setCounty(row.getCell(11).getStringCellValue().trim());
				sale.setAdress(row.getCell(12).getStringCellValue().trim());
				sale.setHousetype(parseByte(row.getCell(13).getStringCellValue().trim()));
				sale.setHouselayout(parseByte(row.getCell(14).getStringCellValue().trim()));
				sale.setBudgetmoney(parseFloat(row.getCell(15).getStringCellValue().trim()));
				sale.setHousearea(parseDouble(row.getCell(16).getStringCellValue().trim()));
				sale.setIntetionid(parseLong(row.getCell(17).getStringCellValue().trim()));
				rows.add(sale);
			}
			// 关闭输入流
			wk.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (bis != null) {
				try {
					bis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return rows; // rows 可以说是 内存中的转化好的表格格式正确文件内容对象
	}
	public static Float parseFloat(String sorce){
		if(isNotEmpty(sorce)){
			try {
				return Float.valueOf(sorce);
			} catch (NumberFormatException e) {
			}
		};
		return null;
	}
	public static Double parseDouble(String sorce){
		if(isNotEmpty(sorce)){
			try {
				return Double.valueOf(sorce);
			} catch (NumberFormatException e) {
			}
		};
		return null;
	}
	public static Byte parseByte(String sorce){
		if(isNotEmpty(sorce)){
			try {
				return Byte.valueOf(sorce);
			} catch (NumberFormatException e) {
			}
		};
		return null;
	}
	public static Long parseLong(String sorce){
		if(isNotEmpty(sorce)){
			try {
				return Long.valueOf(sorce);
			} catch (NumberFormatException e) {
			}
		};
		return null;
	}
	public static Integer parseInteger(String sorce){
		if(isNotEmpty(sorce)){
			try {
				return Integer.valueOf(sorce);
			} catch (NumberFormatException e) {
			}
		};
		return null;
	}
	/**
	 * 存储到本地磁盘
	 * 
	 * @param targetPath
	 * @param upFile
	 * @return
	 * @author spongeli
	 * @throws IOException
	 * @throws IllegalStateException
	 */
	private File saveExcel(String targetPath, MultipartFile upFile) throws IllegalStateException, IOException {
		File f = new File(targetPath); // 实例硬盘中文件夹（路径）对象
		if (!f.exists()) {// 判断此路径/文件夹是否存在
			f.mkdirs(); // 如果不存在 则创建文件夹/目录
		}
		String originalName = upFile.getOriginalFilename();// 获取文件对象原始文件名
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
		String tag = sdf.format(new Date());
		String upFileName = targetPath + File.separator + tag + "_" + originalName;// 拼接出文件的要存储的位置（全路径）
		File file = new File(upFileName);// 创建 内存中的File对象
		if (file.exists()) { // 判断是否存在
			file.delete();// 如果有重名文件存在 就删除文件
			// 这个对象对应的硬盘必须删 不能存在 如果已经存在 则会抛出
			// IOException异常
		}
		upFile.transferTo(file);
		return file;
	}

	/**
	 * 校验字符串非空
	 * 
	 * @param str
	 * @return
	 * @author spongeli
	 */
	public static boolean isNotEmpty(String str) {
		return str != null && str.length() > 0;
	}

	/**
	 * 校验集合非空
	 * 
	 * @param list
	 * @return
	 * @author spongeli
	 */
	public static boolean isNotEmpty(List<?> list) {
		return list != null && list.size() > 0;
	}
}